'use strict';

const {Service} = require('ee-core');
const Storage = require('ee-core/storage');
const _ = require('lodash');
const path = require('path');

/**
 * sqlite数据存储
 * @class
 */
class SqlitedbService extends Service {

    constructor(ctx) {
        super(ctx);

        this.sqliteFile = 'sqlite-demo.db';
        let sqliteOptions = {
            driver: 'sqlite',
            default: {
                timeout: 6000,
                verbose: console.log // 打印sql语法
            }
        }
        this.demoSqliteDB = Storage.connection(this.sqliteFile, sqliteOptions);
    }

    /*
     * 检查并创建表 (sqlite)
     */
    async checkAndCreateTableSqlite(tableName = '') {
        if (_.isEmpty(tableName)) {
            throw new Error(`table name is required`);
        }
        // 检查表是否存在
        const userTable = this.demoSqliteDB.db.prepare('SELECT * FROM sqlite_master WHERE type=? AND name = ?');
        const result = userTable.get('table', tableName);
        //console.log('result:', result);
        if (result) {
            return;
        }

        // 创建表
        const create_table_user =
            `CREATE TABLE ${tableName}
             (
                 id   INTEGER PRIMARY KEY AUTOINCREMENT,
                 name CHAR(50) NOT NULL,
                 age  INT
             );`
        this.demoSqliteDB.db.exec(create_table_user);

    }


    //------------------------------单表------------------------


    //------------------------------查询数据------------------------

    //查询单表数据
    async list(info) {
        let table = 'user';
        await this.checkAndCreateTableSqlite(table);
        const selectUser = this.demoSqliteDB.db.prepare(`SELECT *
                                                         FROM ${table} `);
        return selectUser.all();
    }

    //------------------------------保存数据------------------------

    async save(data) {
        let table = 'user';
        await this.checkAndCreateTableSqlite(table);

        // 构建动态的列名和占位符
        const columns = Object.keys(data).join(', ');  // 获取 data 对象的键（例如：name, age）
        const placeholders = Object.keys(data).map(() => '?').join(', ');  // 生成与数据长度对应的占位符 (例如：?, ?)

        // 构建动态的 SQL 语句
        const insert = this.demoSqliteDB.db.prepare(`INSERT INTO ${table} (${columns})
                                                     VALUES (${placeholders})`);

        // 执行插入，data 数组顺序与占位符一致
        insert.run(Object.values(data));
        return true;
    }

    //------------------------------删除数据------------------------

    async remove() {
        let table = 'user';
        await this.checkAndCreateTableSqlite(table);
        const delUser = this.demoSqliteDB.db.prepare(`DELETE
                                                      FROM ${table}`);
        delUser.run();
        return true;
    }

    async page(pageNum = 1, pageSize = 5){
        let table = 'user';

        // 确保表格已经创建
        await this.checkAndCreateTableSqlite(table);

        // 计算 OFFSET
        const offset = (pageNum - 1) * pageSize;

        // 查询指定页的数据
        const selectUser = this.demoSqliteDB.db.prepare(` SELECT * FROM ${table}  LIMIT ? OFFSET ? `);

        const data = selectUser.all(pageSize, offset);

        // 查询总记录数
        const countQuery = this.demoSqliteDB.db.prepare(`  SELECT COUNT(*) AS total FROM ${table} `);

        const totalCount = countQuery.get().total;

        // 计算总页数
        const totalPages = Math.ceil(totalCount / pageSize);

        // 返回分页数据和总页数
        return {
            data,              // 当前页的数据
            totalCount,        // 总记录数
            totalPages,        // 总页数
            currentPage: pageNum, // 当前页数
            pageSize,          // 每页数据条数
        };
    }


    /*
     * 删 Test data (sqlite)
     */
    async delTestDataSqlite(name = '') {
        //console.log("delete name:", name);

        let table = 'user';
        await this.checkAndCreateTableSqlite(table);

        const delUser = this.demoSqliteDB.db.prepare(`DELETE
                                                      FROM ${table}
                                                      WHERE name = ?`);
        delUser.run(name);

        return true;
    }

    /*
     * 改 Test data (sqlite)
     */
    async updateTestDataSqlite(name = '', age = 0) {
        //console.log("update :", {name, age});

        let table = 'user';
        await this.checkAndCreateTableSqlite(table);

        const updateUser = this.demoSqliteDB.db.prepare(`UPDATE ${table}
                                                         SET age = ?
                                                         WHERE name = ?`);
        updateUser.run(age, name);

        return true;
    }

    /*
     * 查 Test data (sqlite)
     */
    async getTestDataSqlite(age = 0) {
        //console.log("select :", {age});

        let table = 'user';
        await this.checkAndCreateTableSqlite(table);

        const selectUser = this.demoSqliteDB.db.prepare(`SELECT *
                                                         FROM ${table}
                                                         WHERE age = @age`);
        const users = selectUser.all({age: age});
        //console.log("select users:", users);
        return users;
    }


    /*
     * all Test data (sqlite)
     */
    async getAllTestDataSqlite() {
        //console.log("select all user");

        let table = 'user';
        await this.checkAndCreateTableSqlite(table);

        const selectAllUser = this.demoSqliteDB.db.prepare(`SELECT *
                                                            FROM ${table} `);
        const allUser = selectAllUser.all();
        //console.log("select allUser:", allUser);
        return allUser;
    }

    /*
     * get data dir (sqlite)
     */
    async getDataDir() {
        const dir = this.demoSqliteDB.getStorageDir();

        return dir;
    }

    /*
     * set custom data dir (sqlite)
     */
    async setCustomDataDir(dir) {
        if (_.isEmpty(dir)) {
            return;
        }

        // the absolute path of the db file
        const dbFile = path.join(dir, this.sqliteFile);
        const sqliteOptions = {
            driver: 'sqlite',
            default: {
                timeout: 6000,
                verbose: console.log
            }
        }
        this.demoSqliteDB = Storage.connection(dbFile, sqliteOptions);

        return;
    }
}

SqlitedbService.toString = () => '[class SqlitedbService]';
module.exports = SqlitedbService;
